﻿using System;
using System.Collections;
using System.Collections.Generic;
using Huirui.Cavan.Domain.MainBoundedContext.SearchModule;
using Huirui.Cavan.Domain.MainBoundedContext.PurchaseModule;
using Huirui.Cavan.Data.Seedwork;
using Huirui.Cavan.Domain.MainBoundedContext.ReportModule;
using Huirui.Cavan.Core.Extension;
using System.Text;
using NHibernate;
using Huirui.Cavan.Core.Helper;
using Spring.Data.Common;
using System.Data;

namespace Huirui.Cavan.Infrastructure.Data.MainBoundedContext.PurchaseModule
{
    #region PurchaseOrder

    public class PurchaseOrderRepository : Repository<PurchaseOrder, PurchaseOrderSearch>, IPurchaseOrderRepository
    {
        public IList<PurchaseOrderReport> GetPurchaseOrderReport(DateTime? startDate, DateTime? endDate, string purchaseOrderCode, string supplierCode, IList<PurchaseIdentifier.StockInStatus> stockInStatus)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT po.SupplierCode, ISNULL(s.Name, '') AS SupplierName, pod.PurchaseOrderCode, po.StockInStatus, po.PurchaseEmployeeCode, ");
            sql.Append("pod.Number, pod.UnitCost, pod.WholeAmount, pod.UnStockInNumber, pod.UnStockInAmount, po.CreateDate, pod.StockInDate ");
            sql.Append("FROM   PurchaseOrderDetail pod WITH (NOLOCK) INNER JOIN PurchaseOrder po WITH(NOLOCK) ON  po.Code = pod.PurchaseOrderCode ");
            sql.Append("LEFT JOIN Supplier s WITH (NOLOCK) ON  s.Code = po.SupplierCode ");
            sql.Append("WHERE  1 = 1 ");
            if (startDate.HasValue && endDate.HasValue)
            {
                sql.Append("AND  po.CreateDate >= @StartDate AND po.CreateDate <= @EndDate ");
            }
            if (supplierCode.IsNotEmpty())
            {
                sql.Append("AND s.Code = @SupplierCode ");
            }
            if (purchaseOrderCode.IsNotEmpty())
            {
                sql.Append("AND po.Code = @PurchaseOrderCode ");
            }
            if (stockInStatus.IsNotNull() && stockInStatus.Count > 0)
            {
                StringBuilder sisSB = new StringBuilder();
                foreach (PurchaseIdentifier.StockInStatus sis in stockInStatus)
                {
                    sisSB.Append(sis.GetHashCode().ToString() + ",");
                }
                sql.Append("AND po.StockInStatus IN ( " + StringHelper.ClearLastChar(sisSB.ToString()) + ")");
            }

            IDbParameters paras = this.AdoTemplate.CreateDbParameters();

            if (startDate.HasValue && endDate.HasValue)
            {
                paras.Add("StartDate", DbType.DateTime).Value = startDate.Value;
                paras.Add("EndDate", DbType.DateTime).Value = endDate.Value;
            }
            if (supplierCode.IsNotEmpty())
            {
                paras.Add("SupplierCode", DbType.String).Value = supplierCode;
            }
            if (purchaseOrderCode.IsNotEmpty())
            {
                paras.Add("PurchaseOrderCode", DbType.String).Value = purchaseOrderCode;
            }

            return this.AdoTemplate.QueryWithRowMapper<PurchaseOrderReport>(CommandType.Text, sql.ToString(), new PurchaseOrderReportMapper(), paras);
        }

        public IList<PurchaseOrderManageReport> GetPurchaseOrderManageReport(DateTime? startDate, DateTime? endDate, string supplierCode, string purchaseOrderCode,
            string supplierInvoiceNumber, string purchaseEmployeeCode, string wareHouseCode, string checkedStatues, string stockInStatues)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT po.Code, po.PurchaseDate, ISNULL(s.Name, '') AS SupplierName, ISNULL(po.SupplierInvoiceNumber, '') AS SupplierInvoiceNumber, po.Currency, po.CurrencyRate, ");
            sql.Append("t.Amount, ISNULL(p.Name, '') AS ProjectName, po.PurchaseEmployeeCode, po.DeliveryDate, po.WareHouseCode, w.Address, po.StockInStatus, ");
            sql.Append("ua.EmployeeCode AS ProducerEmployeeCode, ISNULL(po.CheckEmployeeCode, '') AS CheckEmployeeCode, po.CheckedStatus ");
            sql.Append("FROM PurchaseOrder po INNER JOIN ( ");
            sql.Append("SELECT pod.PurchaseOrderCode, SUM(pod.Number * pod.UnitCost) AS Amount ");
            sql.Append("FROM PurchaseOrderDetail pod WITH(NOLOCK) ");
            sql.Append("GROUP BY pod.PurchaseOrderCode ");
            sql.Append(") AS t ON po.Code = t.PurchaseOrderCode ");
            sql.Append("LEFT JOIN Supplier s WITH (NOLOCK) ON s.Code = po.SupplierCode ");
            sql.Append("LEFT JOIN Project p WITH (NOLOCK) ON p.Code = po.ProjectCode ");
            sql.Append("LEFT JOIN WareHouse w WITH (NOLOCK) ON po.WareHouseCode = w.Code ");
            sql.Append("INNER JOIN UserAccount ua WITH (NOLOCK) ON po.EditUser = ua.ID ");
            sql.Append("WHERE  1 = 1 ");
            if (startDate.HasValue && endDate.HasValue)
            {
                sql.Append("AND  po.CreateDate >= @StartDate AND po.CreateDate <= @EndDate ");
            }
            if (supplierCode.IsNotEmpty())
            {
                sql.Append("AND s.Code = @SupplierCode ");
            }
            if (purchaseOrderCode.IsNotEmpty())
            {
                sql.Append("AND po.Code = @PurchaseOrderCode ");
            }
            if (wareHouseCode.IsNotEmpty())
            {
                sql.Append("AND po.WareHouseCode = @WareHouseCode ");
            }
            if (checkedStatues.IsNotEmpty())
            {
                sql.Append("AND po.CheckedStatus in (@CheckedStatus)");
            }
            if (stockInStatues.IsNotEmpty())
            {
                sql.Append("AND po.StockInStatus in (@StockInStatus)");
            }
            IDbParameters paras = this.AdoTemplate.CreateDbParameters();

            if (startDate.HasValue && endDate.HasValue)
            {
                paras.Add("StartDate", DbType.DateTime).Value = startDate.Value;
                paras.Add("EndDate", DbType.DateTime).Value = endDate.Value;
            }
            if (supplierCode.IsNotEmpty())
            {
                paras.Add("SupplierCode", DbType.String).Value = supplierCode;
            }
            if (purchaseOrderCode.IsNotEmpty())
            {
                paras.Add("PurchaseOrderCode", DbType.String).Value = purchaseOrderCode;
            }
            if (wareHouseCode.IsNotEmpty())
            {
                paras.Add("WareHouseCode", DbType.String).Value = wareHouseCode;
            }
            if (checkedStatues.IsNotEmpty())
            {
                paras.Add("CheckedStatus", DbType.String).Value = checkedStatues;
            }
            if (stockInStatues.IsNotEmpty())
            {
                paras.Add("StockInStatus", DbType.String).Value = stockInStatues;
            }
            return this.AdoTemplate.QueryWithRowMapper<PurchaseOrderManageReport>(CommandType.Text, sql.ToString(), new PurchaseOrderManageReportMapper(), paras);
        }
    }
    #endregion
}